using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
//using System.Data.OleDb;
//using System.Data.OracleClient;
//using MySql.Data.MySqlClient;

namespace NET.Library.Data
{
    /// <summary>
    /// Wrapper class for Microsoft.Practices.EnterpriseLibrary.Data.
    /// </summary>
    //public abstract class DB : IDB
    public class DB : IDB
    {
        #region Fields
        ///// <summary>
        ///// Private variable used by database connection string property
        ///// </summary>
        //private string _connectionString;

        #endregion

        #region Properties
        /// <summary>
        /// Property to store and retrieve database connection string name
        /// </summary>
        public string ConnectionString { get; set; }
        #endregion

        #region Constructors
        /// <summary>
        /// DB Constructor
        /// </summary>
        /// <param name="connectionStringName"></param>
        public DB(string connectionStringName)
        {
            this.ConnectionString = connectionStringName;
        }

        /// <summary>
        /// DB Constructor
        /// </summary>
        public DB()
        { }
        #endregion

        #region Instance Methods
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a DbCommand object and returns the results as a DataSet.
        /// </summary>
        /// <param name="dbCommand">The DbCommand object to use.</param>
        /// <returns>Result as a DataSet.</returns>
        ///----------------------------------------------------------------------------------
        public virtual DataSet DBExecuteDataSet(DbCommand dbCommand)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

            try
            {
                return db.ExecuteDataSet(dbCommand);
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with parameters within a transaction and returns the results as a DataSet.
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns>Result as a DataSet.</returns>
        ///----------------------------------------------------------------------------------
        public virtual DataSet DBExecuteDataSetWithTransAction(string storedProcedureName, params object[] parameterValues)
        {
            DataSet ds = null;
            Database db;
            DbCommand dbCommand;
            db = DatabaseFactory.CreateDatabase(this.ConnectionString);
            dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
            DbConnection con = db.CreateConnection();
            con.Open();
            DbTransaction trans = con.BeginTransaction();

            try
            {

                ds = db.ExecuteDataSet(dbCommand, trans);
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }

            return ds;
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes the named Stored Procedure with no parameters and returns multiple named DataTables
        /// </summary>
        /// <remarks>Uses the MS Patterns and Practices Data Access Application Block via the NET.Library Helper.</remarks>
        /// <param name="storedProcedureName">String name of the SQL Stored Proc</param>
        /// <param name="tableNames">Comma delimeted list of names to assign to newly created tables</param>
        /// <returns>Dataset from the website database</returns>
        ///----------------------------------------------------------------------------------
        public virtual DataSet DBExecuteStoredProc(string storedProcedureName, string[] tableNames)
        {
            object[] emptyArray = new object[0];
            return DBExecuteStoredProc(storedProcedureName, emptyArray, tableNames);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes the named Stored Procedure with no parameters and returns multiple named DataTables
        /// </summary>
        /// <remarks>Uses the MS Patterns and Practices Data Access Application Block via the NET.Library Helper.</remarks>
        /// <param name="storedProcedureName">String name of the SQL Stored Proc</param>
        /// <param name="parameterValues"></param>
        /// <param name="tableNames">Comma delimeted list of names to assign to newly created tables</param>
        /// <returns>Dataset from the website database</returns>
        ///----------------------------------------------------------------------------------
        public virtual DataSet DBExecuteStoredProc(string storedProcedureName, object[] parameterValues, string[] tableNames)
        {
            DataSet returnDS = DBExecuteDataSet(storedProcedureName, parameterValues);
            for (int i = 0; i < returnDS.Tables.Count; i++)
            {
                if (tableNames[i] != null)
                {
                    returnDS.Tables[i].TableName = tableNames[i].Trim();
                }
            }
            return returnDS;
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// DBExecuteStoredProc
        /// </summary>
        /// <param name="storedProcName">The name of the insert stored procedure to execute.</param>
        /// <param name="dsProcParams">A dataset of parameters to pass to the stored procedure.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public DataSet DBExecuteStoredProc(string storedProcName, DataSet dsProcParams)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
            DbCommand dbCommd;// = db.getc;// = new DbCommand();
            DataSet ds = new DataSet();

            try
            {
                //dbCommd.CommandType = CommandType.StoredProcedure;
                //dbCommd.CommandText = storedProcName;

                if (dsProcParams.Tables.Count > 0)
                {
                    //PopulateDbCommandFromDataTable(ref dbCommd, dsProcParams.Tables[0]);

                    dbCommd = db.GetStoredProcCommand(storedProcName);

                    PopulateDbCommandFromDataTable(ref dbCommd, dsProcParams.Tables[0]);

                    try
                    {
                        ds = DBExecuteDataSet(dbCommd);
                    }
                    finally
                    {
                        if (dbCommd.Connection.State != ConnectionState.Closed) { dbCommd.Connection.Close(); }
                        dbCommd.Dispose();
                    }
                }

                return ds; //DBExecuteDataSet(dbCommd);
            }
            finally
            {
                db = null;
            }
        }

        #region IDataReader
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public virtual IDataReader DBExecuteReader(string storedProcedureName)
        {
            return DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public virtual IDataReader DBExecuteReader(string storedProcedureName, params object[] parameterValues)
        {
            return DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName, parameterValues);
        }
        #endregion

        #region DataSet
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <returns>DataSet</returns>
        ///----------------------------------------------------------------------------------
        public DataSet DBExecuteDataSet(string storedProcedureName)
        {
            Microsoft.Practices.EnterpriseLibrary.Data.Database db;
            DbCommand dbCommand;

            db = DatabaseFactory.CreateDatabase(this.ConnectionString);
            dbCommand = db.GetStoredProcCommand(storedProcedureName);
            try
            {
                return db.ExecuteDataSet(dbCommand);
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>DataSet</returns>
        ///----------------------------------------------------------------------------------
        public DataSet DBExecuteDataSet(string storedProcedureName, params object[] parameterValues)
        {
            Database db;
            DbCommand dbCommand;

            db = DatabaseFactory.CreateDatabase(this.ConnectionString);
            dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
            try
            {
                return db.ExecuteDataSet(dbCommand);
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }
        #endregion

        #region ExecuteNonQuery
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public int DBExecuteNonQuery(string storedProcedureName)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
            try
            {
                return db.ExecuteNonQuery(dbCommand);
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public int DBExecuteNonQuery(string storedProcedureName, params object[] parameterValues)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
            try
            {
                return db.ExecuteNonQuery(dbCommand);
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a DbCommand object and returns the number of rows affected.
        /// </summary>
        /// <param name="dbCommand">The DbCommand object to use.</param>
        /// <returns>The number of rows affected.</returns>
        ///----------------------------------------------------------------------------------
        public int DBExecuteNonQuery(DbCommand dbCommand)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
            try
            {
                return db.ExecuteNonQuery(dbCommand);
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }
        #endregion

        #region ExecuteScalar
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// DBExecuteScalar
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <returns>object</returns>
        ///----------------------------------------------------------------------------------
        public object DBExecuteScalar(string storedProcedureName)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
            try
            {
                return db.ExecuteScalar(dbCommand);
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        /// <summary>
        /// DBExecuteScalar
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns></returns>
        public object DBExecuteScalar(string storedProcedureName, params object[] parameterValues)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
            try
            {
                return db.ExecuteScalar(dbCommand);
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        public object DBExecuteScalar(DbCommand dbCommd)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
            try
            {
                return db.ExecuteScalar(dbCommd);
            }
            finally
            {
                if (dbCommd.Connection.State != ConnectionState.Closed) { dbCommd.Connection.Close(); }
                dbCommd.Dispose();
                if (db != null) db = null;
            }
        }
        #endregion

        #region Misc

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>True if the query was executed sucessfully, else false.</returns>
        ///----------------------------------------------------------------------------------
        public bool DBExecuteNonQuerybool(string storedProcedureName, params object[] parameterValues)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
            try
            {
                return db.ExecuteNonQuery(storedProcedureName, parameterValues) == 0 ? false : true;
            }
            finally
            {
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure and returns the value of the parameter called ReturnValue.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>The value of the parameter called ReturnValue.</returns>
        ///----------------------------------------------------------------------------------
        public int DBExecuteNonQueryReturnValue(string storedProcedureName, params object[] parameterValues)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

            DbCommand dbCmd = db.GetStoredProcCommand(storedProcedureName, parameterValues);

            try
            {
                return (int)db.ExecuteScalar(dbCmd);
            }
            finally
            {
                dbCmd.Connection.Close();
                dbCmd.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Wrapper method to return a StoredProcCommand.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns>DbCommand</returns>
        ///----------------------------------------------------------------------------------
        public DbCommand DBGetStoredProcCommand(string storedProcedureName)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

            try
            {
                return db.GetStoredProcCommand(storedProcedureName);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Wrapper method to return a StoredProcCommand.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues"></param>
        /// <returns>DbCommand</returns>
        ///----------------------------------------------------------------------------------
        public DbCommand DBGetStoredProcCommand(string storedProcedureName, params object[] parameterValues)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

            try
            {
                return db.GetStoredProcCommand(storedProcedureName, parameterValues);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        public void DBAddInParameter(DbCommand dbCommand, string name, DbType dbType)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

            try
            {
                db.AddInParameter(dbCommand, name, dbType);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        public void DBAddInParameter(DbCommand dbCommand, string name, DbType dbType, object value)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

            try
            {
                db.AddInParameter(dbCommand, name, dbType, value);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        public void DBAddOutParameter(DbCommand dbCommand, string name, DbType dbType, int size)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

            try
            {
                db.AddOutParameter(dbCommand, name, dbType, size);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        public void DBAddReturnValueParameter(DbCommand dbCommand, string name, DbType dbType, int size, string sourceColumn)
        {
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

            try
            {
                db.AddParameter(dbCommand, name, dbType, size, ParameterDirection.ReturnValue, false, 0, 0, sourceColumn, DataRowVersion.Current, null);
            }
            finally
            {
                if (db != null) db = null;
            }
        }
        #endregion

        #endregion

        #region Static Methods

        #region ExecuteReader

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure and returns the results as an IDataReader.
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <returns>Result as an IDataReader</returns>
        /// <example>
        /// IDataReader dr = DB.ExecuteReader(SP_ADDRESS_GETADDRESSBYID, addressId);
        ///    try
        ///    {
        ///        while (dr.Read())
        ///        {
        ///            Address newAddress = new Address(
        ///                GetDBInt(dr, "ID"),
        ///                GetDBString(dr, "Address1"),
        ///                GetDBString(dr, "Address2"),
        ///                GetDBString(dr, "Address3"),
        ///                GetDBString(dr, "Town"),
        ///                GetDBInt(dr, "CountyID"),
        ///                GetDBInt(dr, "CountryID"),
        ///                GetDBString(dr, "Postcode"));
        ///            return newAddress;
        ///        }
        ///
        ///        return null;
        ///    }
        ///    finally
        ///    {
        ///        dr.Close();
        ///    }
        /// </example>
        ///----------------------------------------------------------------------------------
        public static IDataReader ExecuteReader(string storedProcedureName)
        {
            return DatabaseFactory.CreateDatabase().ExecuteReader(storedProcedureName);


        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with parameterValues and returns the results as an IDataReader.
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>Result as an IDataReader</returns>
        ///----------------------------------------------------------------------------------
        public static IDataReader ExecuteReader(string storedProcedureName, params object[] parameterValues)
        {
            return DatabaseFactory.CreateDatabase().ExecuteReader(storedProcedureName, parameterValues);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a DbCommand object and returns as an IDataReader.
        /// </summary>
        /// <param name="dbCommand"></param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public static IDataReader ExecuteReader(DbCommand dbCommand)
        {
            return DatabaseFactory.CreateDatabase().ExecuteReader(dbCommand);
        }
        #endregion

        #region ExecuteDataSet

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure and returns the results as a DataSet.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns>Result as a DataSet</returns>
        ///----------------------------------------------------------------------------------
        public static DataSet ExecuteDataSet(string storedProcedureName)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            dbCommand = db.GetStoredProcCommand(storedProcedureName);
            try
            {
                DataSet ds = db.ExecuteDataSet(dbCommand);
                return ds;
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with parameterValues and returns the results as a DataSet.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>Result as a DataSet.</returns>
        ///----------------------------------------------------------------------------------
        public static DataSet ExecuteDataSet(string storedProcedureName, params object[] parameterValues)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
            try
            {
                DataSet ds = db.ExecuteDataSet(dbCommand);
                return ds;
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a DbCommand object and returns the results as a DataSet.
        /// </summary>
        /// <param name="dbCommand">The DbCommand object to use.</param>
        /// <returns>Result as a DataSet.</returns>
        ///----------------------------------------------------------------------------------
        public static DataSet ExecuteDataSet(DbCommand dbCommand)
        {
            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                DataSet ds = db.ExecuteDataSet(dbCommand);
                return ds;
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbCommd"></param>
        /// <returns></returns>
        public static object ExecuteScalar(DbCommand dbCommd)
        {
            Database db;
            db = DatabaseFactory.CreateDatabase();
            try
            {
                return db.ExecuteScalar(dbCommd);
            }
            finally
            {
                if (dbCommd.Connection.State != ConnectionState.Closed) { dbCommd.Connection.Close(); }
                dbCommd.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns>object</returns>
        ///----------------------------------------------------------------------------------
        public static object ExecuteScalar(string storedProcedureName)
        {
            Database db;
            DbCommand dbCommand;
            db = DatabaseFactory.CreateDatabase();
            dbCommand = db.GetStoredProcCommand(storedProcedureName);
            try
            {
                object returnObject = db.ExecuteScalar(dbCommand);
                //dbCommand.Connection.Close();
                return returnObject;
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>object</returns>
        ///----------------------------------------------------------------------------------
        public static object ExecuteScalar(string storedProcedureName, params object[] parameterValues)
        {
            Database db;
            DbCommand dbCommand;
            db = DatabaseFactory.CreateDatabase();
            dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
            try
            {
                object returnObject = db.ExecuteScalar(dbCommand);
                //dbCommand.Connection.Close();
                return returnObject;
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
            //return DatabaseFactory.CreateDatabase().ExecuteScalar(storedProcedureName, parameterValues);
        }

        /// <summary>
        /// ExecuteScalarBool
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="dsParams"></param>
        /// <returns></returns>
        public static bool ExecuteScalarBool(string spName, DataSet dsParams)
        {
            //SqlCommand sqlComm = new SqlCommand();

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommd = db.GetStoredProcCommand(spName);

            DataSet ds = new DataSet();

            try
            {
                //dbCommd.CommandType = CommandType.StoredProcedure;
                //dbCommd.CommandText = storedProcName;

                if (dsParams.Tables.Count > 0)
                {
                    //PopulateDbCommandFromDataTable(ref dbCommd, dsProcParams.Tables[0]);

                    dbCommd = db.GetStoredProcCommand(spName);

                    PopulateDbCommandFromDataTable(ref dbCommd, dsParams.Tables[0]);

                    try
                    {
                        ds = ExecuteDataSet(dbCommd);
                    }
                    finally
                    {
                        if (dbCommd.Connection.State != ConnectionState.Closed) { dbCommd.Connection.Close(); }
                        dbCommd.Dispose();
                    }
                }

                return (bool)ExecuteScalar(dbCommd); ; //DBExecuteDataSet(dbCommd);
            }
            finally
            {
                db = null;
            }

            //try
            //{
            //    //sqlComm.CommandType = CommandType.StoredProcedure;
            //    //dbCommd.CommandType = CommandType.StoredProcedure;
            //    PopulateDbCommandFromDataTable(ref dbCommd, dsParams.Tables[0]);


            //    //PopulateSQLCommandFromDataTable(sqlComm, dsParams.Tables[0]);
            //    //sqlComm.CommandText = spName;
            //    //return (bool)DB.ExecuteScalar(sqlComm);
            //}
            //finally
            //{
            //    //sqlComm.Dispose();
            //}
        }

        #endregion

        #region ExecuteNonQuery

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public static int ExecuteNonQuery(string storedProcedureName)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
            try
            {
                int rows = db.ExecuteNonQuery(dbCommand);
                return rows;

            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes the storedProcedureName using the given parameterValues and returns the number of rows affected.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>The number of rows affected.</returns>
        ///----------------------------------------------------------------------------------
        public static int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
            try
            {
                int rows = db.ExecuteNonQuery(dbCommand);
                return rows;
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
            //return DatabaseFactory.CreateDatabase().ExecuteNonQuery(storedProcedureName, parameterValues);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a DbCommand object and returns the number of rows affected.
        /// </summary>
        /// <param name="dbCommand">The DbCommand object to use.</param>
        /// <returns>The number of rows affected.</returns>
        ///----------------------------------------------------------------------------------
        public static int ExecuteNonQuery(DbCommand dbCommand)
        {
            Database db = DatabaseFactory.CreateDatabase();
            try
            {
                int rows = db.ExecuteNonQuery(dbCommand);
                return rows;
            }
            finally
            {
                dbCommand.Connection.Close();
                //dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        //public static void CreateConnection()
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    db.CreateConnection();
        //}

        public static int ExecuteNonQueryInTransaction(DbCommand dbCommand)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int retValue = 0;

            try
            {
                using (DbConnection connection = db.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        db.ExecuteNonQuery(dbCommand, transaction);

                        retValue = (int)dbCommand.Parameters["@RETURN_VALUE"].Value;

                        if (retValue == -1)
                        {
                            transaction.Rollback();
                            return -1;
                        }
                        //Commit the transaction
                        transaction.Commit();
                    }
                    catch
                    {
                        //Roll back the transaction.
                        transaction.Rollback();
                        return -3;
                    }
                    connection.Close();
                }
            }
            finally
            {
                if (dbCommand.Connection.State != ConnectionState.Closed) { dbCommand.Connection.Close(); }
                if (db != null) db = null;
            }
            return retValue;
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Uses ExecuteNonQuery in a specified Transaction
        /// </summary>
        /// <param name="transaction">The IDbTransaction to execute the command within.</param>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns>The number of rows affected.</returns>
        ///----------------------------------------------------------------------------------
        public static int ExecuteNonQuery(IDbTransaction transaction, string storedProcedureName)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
            DbTransaction trans = (DbTransaction)transaction;
            dbCommand.Transaction = trans;
            try
            {
                int rows = db.ExecuteNonQuery(dbCommand);
                return rows;
                //}
                //finally
                //{
                //    dbCommand.Connection.Close();
                //}
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
            //DbTransaction trans = (DbTransaction)transaction;
            //return DatabaseFactory.CreateDatabase().ExecuteNonQuery(trans, CommandType.StoredProcedure, storedProcedureName);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes the <i>storedProcedureName</i> using the given parameterValues within a transaction and returns the number of rows affected.
        /// </summary>
        /// <param name="transaction">The IDbTransaction to execute the command within.</param>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>The number of rows affected.</returns>
        ///----------------------------------------------------------------------------------
        public static int ExecuteNonQuery(IDbTransaction transaction, string storedProcedureName, params object[] parameterValues)
        {
            //Database db = DatabaseFactory.CreateDatabase();
            //DbCommand dbCommand = db..GetStoredProcCommand(storedProcedureName);
            //try
            //{
            //    int rows = db.ExecuteNonQuery(dbCommand);
            //    return rows;
            //}
            ////return DatabaseFactory.CreateDatabase().ExecuteNonQuery(storedProcedureName);
            //finally
            //{
            //    dbCommand.Connection.Close();
            //}
            DbTransaction trans = (DbTransaction)transaction;
            return DatabaseFactory.CreateDatabase().ExecuteNonQuery(trans, storedProcedureName, parameterValues);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Uses ExecuteNonQuery in a Transaction. If an exception oocurs the transaction is rolled back.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns>The number of rows affected.</returns>
        ///----------------------------------------------------------------------------------
        public static int ExecuteNonQueryTrans(string storedProcedureName)
        {
            int rows = 0;
            Database db = DatabaseFactory.CreateDatabase();
            using (DbConnection dbConn = db.CreateConnection())
            {
                DbTransaction transaction = dbConn.BeginTransaction();
                try
                {
                    rows = ExecuteNonQuery(storedProcedureName);
                    // Commit the transaction.
                    transaction.Commit();
                }
                catch
                {
                    // Roll back the transaction. 
                    transaction.Rollback();
                }
                finally
                {
                    dbConn.Close();
                }
                return rows;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>True if the query was executed sucessfully, else false.</returns>
        ///----------------------------------------------------------------------------------
        public static bool ExecuteNonQuerybool(string storedProcedureName, params object[] parameterValues)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
            try
            {
                bool returnValue = db.ExecuteNonQuery(storedProcedureName, parameterValues) == 0 ? false : true;
                return returnValue;
                //}
                ////return DatabaseFactory.CreateDatabase().ExecuteNonQuery(storedProcedureName);
                //finally
                //{
                //    dbCommand.Connection.Close();
                //}
            }
            finally
            {
                //dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }
            //return DB.ExecuteNonQuery(storedProcedureName, parameterValues) == 0 ? false : true;
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure and returns the value of the parameter called ReturnValue.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>The value of the parameter called ReturnValue.</returns>
        ///----------------------------------------------------------------------------------
        public static int ExecuteNonQueryReturnValue(string storedProcedureName, params object[] parameterValues)
        {
            Database db = DatabaseFactory.CreateDatabase();
            //DbCommand dbCmd = db.GetStoredProcCommand(storedProcedureName, parameterValues);
            //int i = parameterValues.GetUpperBound(0);

            //ArrayList li = new ArrayList();

            //foreach (object Object in parameterValues)
            //{
            //    li.Add(Object);
            //}

            //li.Add(null);

            //Array objectArray = Array.CreateInstance(typeof(object), li.Count);

            //li.CopyTo(objectArray);

            //parameterValues.
            //parameterValues.SetValue(0, i + 1);
            //DbCommand dbCmd = db.GetStoredProcCommand(storedProcedureName, objectArray);
            DbCommand dbCmd = db.GetStoredProcCommand(storedProcedureName, parameterValues);
            //ParameterCache ParameterCache = new ParameterCache();
            //DbParameter[] paramters = ParameterCache..GetCachedParameterSet((db.ConnectionStringWithoutCredentials, storedProcedureName);
            //db.DiscoverParameters(dbCommand);
            //ParameterCache.
            //DbParameterCollection dbParams;
            //foreach(object Object in parameterValues)
            //{
            //    DbParameter dbParam = dbCmd.CreateParameter();
            //    dbParam.Direction = ParameterDirection.Input;
            //    dbParam.Value = Object;
            //    //dbParams.Add(Object);
            //    dbCmd.Parameters.Add(dbParam);
            //}

            ////dbCmd.Parameters.AddRange(dbParams);
            //DbParameter param = dbCmd.CreateParameter();
            //param.Direction = ParameterDirection.Output;
            //param.DbType = DbType.Int32;
            //param.ParameterName = "ReturnValue";
            //dbCmd.Parameters.Add(param);

            //db.
            //sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int, 4, "return");
            //sqlCommand.Parameters.AddRange(parameterValues);
            //dbCommand.Parameters.AddRange(parameterValues);
            try
            {
                //db.ExecuteNonQuery(dbCmd);
                //int retunValue = db.ExecuteNonQuery(dbCmd);
                int retunValue = (int)db.ExecuteScalar(dbCmd);

                //return (int)sqlCommand.Parameters["ReturnValue"].Value;
                //int retunValue = (int)dbCmd.Parameters["ReturnValue"].Value;
                //dbCommand.Connection.Close();
                return retunValue;
            }
            finally
            {
                dbCmd.Connection.Close();
                dbCmd.Dispose();
                if (db != null) db = null;
            }
        }

        ///// <summary>
        ///// 
        ///// </summary>
        ///// <param name="storedProcedureName"></param>
        ///// <param name="paramNameToReturn"></param>
        ///// <param name="parameterValues"></param>
        ///// <returns></returns>
        //public static int ExecuteNonQueryReturnValue(string storedProcedureName, string paramNameToReturn, params object[] parameterValues)
        //{
        //    Database db;
        //    DbCommand dbCommand;
        //    db = DatabaseFactory.CreateDatabase();
        //    dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        //    try
        //    {
        //        db.ExecuteNonQuery(dbCommand);
        //        int retunValue = (int)dbCommand.Parameters[paramNameToReturn].Value;
        //        return retunValue;
        //    }
        //    finally
        //    {
        //        dbCommand.Connection.Close();
        //        dbCommand.Dispose();
        //        if (db != null) db = null;
        //    }
        //}
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure in a transaction and returns the value of the parameter called ReturnValue.
        /// </summary>
        /// <param name="transaction">The IDbTransaction to execute the command within.</param>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="paramIndexToReturn">The index number of the return parameter.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>The value of the parameter called ReturnValue.</returns>
        ///----------------------------------------------------------------------------------
        public static int ExecuteNonQueryReturnValue(IDbTransaction transaction,
            string storedProcedureName,
            int paramIndexToReturn,
            params object[] parameterValues)
        {
            Database db;
            DbCommand dbCommand;
            db = DatabaseFactory.CreateDatabase();
            dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
            DbTransaction trans = (DbTransaction)transaction;
            //ParameterCache ParameterCache = new ParameterCache();
            //DbParameter[] paramters = ParameterCache.GetCachedParameterSet(db.ConnectionStringWithoutCredentials, storedProcedureName);
            //db.DiscoverParameters(dbCommand);
            //db.
            //sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int, 4, "return");
            //sqlCommand.Parameters.AddRange(parameterValues);
            //dbCommand.Parameters.AddRange(parameterValues);

            //db.ExecuteNonQuery(trans, storedProcedureName, parameterValues);
            try
            {
                db.ExecuteNonQuery(dbCommand, trans);

                //return (int)sqlCommand.Parameters["ReturnValue"].Value;
                //return (int)dbCommand.Parameters["ReturnValue"].Value;
                //int returnValue = (int)dbCommand.Parameters["return"].Value;
                int returnValue = (int)dbCommand.Parameters[paramIndexToReturn].Value;
                //dbCommand.Connection.Close();
                return returnValue;
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                if (db != null) db = null;
            }

        }

        #endregion

        #region Misc

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public static int RunSPReturnInteger(string storedProcedureName, params object[] parameterValues)
        {
            Database db;
            //SqlCommandWrapper sqlCommandWrapper;
            DbCommand dbCommd;
            db = DatabaseFactory.CreateDatabase();
            //sqlCommandWrapper = (SqlCommandWrapper)db.GetStoredProcCommandWrapper(storedProcedureName);
            dbCommd=db.GetStoredProcCommand(storedProcedureName);

            //sqlCommandWrapper.AddParameter("@retval", DbType.Int32, ParameterDirection.ReturnValue, "", DataRowVersion.Current, "return");
            //dbCommd.Parameters.Add("@retval", SqlDbType.Int, 4, "return");

            DbParameter prm = dbCommd.CreateParameter();
            prm.ParameterName = "retval";
            prm.DbType = DbType.Int32;
            prm.Value = 0;
            
            //db.ExecuteNonQuery(sqlCommandWrapper);
            try
            {
                db.ExecuteNonQuery(dbCommd);

                //return (int)sqlCommandWrapper.GetParameterValue("retval");
                int returnValue = (int)dbCommd.Parameters["retval"].Value;
                //sqlCommand.Connection.Close();
                return returnValue;
            }
            finally
            {
                if (dbCommd.Connection.State != ConnectionState.Closed) { dbCommd.Connection.Close(); }
                        dbCommd.Dispose();
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, 
        /// updated, or deleted row in a single table in a DataSet. 
        /// When calling this method, your DataSet table must have it's PrimaryKey specified and be the first column in the table. 
        /// This is the item that will be used to uniquely identify each row in the 
        /// DataSet table that has changed.
        /// </summary>
        /// <param name="dataSet">The DataSet to update.</param>
        /// <param name="insertstoredProcedureName">The name of the insert stored procedure to execute.</param>
        /// <param name="updatestoredProcedureName">The name of the update stored procedure to execute.</param>
        /// <param name="deletestoredProcedureName">The name of the delete stored procedure to execute.</param>
        /// <returns>Affected row count as integer.</returns>
        ///----------------------------------------------------------------------------------
        public static int UpdateDataSet(DataSet dataSet,
            string insertstoredProcedureName,
            string updatestoredProcedureName,
            string deletestoredProcedureName)
        {
            string[] columnParams;
            string tableName, primaryKeyField;
            ArrayList columnNames = new ArrayList();
            Database db = DatabaseFactory.CreateDatabase();

            // Get the table that will be modified
            DataTable table = dataSet.Tables[0];
            tableName = table.TableName;

            primaryKeyField = table.Columns[0].ColumnName;//table.PrimaryKey[0].ColumnName;

            //Insert Command
            //Get list of column names for paramaters
            foreach (DataColumn dc in table.Columns)
            {

                //if (dc.ColumnName != primaryKeyField)
                // if(dc.Ordinal != 0)
                columnNames.Add(dc.ColumnName);
            }

            columnNames.Add(table.Columns[0].ColumnName);

            columnParams = (string[])columnNames.ToArray(typeof(string));

            DbCommand insertCommand = db.GetStoredProcCommandWithSourceColumns(insertstoredProcedureName, columnParams);

            //Delete Command
            DbCommand deleteCommand = db.GetStoredProcCommand(deletestoredProcedureName);
            db.AddInParameter(deleteCommand, primaryKeyField, DbType.Int32, primaryKeyField, DataRowVersion.Current);

            //Update Command
            //Get list of column names for paramaters
            columnNames.Clear();
            foreach (DataColumn dc in table.Columns)
            {
                columnNames.Add(dc.ColumnName);
            }

            columnParams = (string[])columnNames.ToArray(typeof(string));
            DbCommand updateCommand = db.GetStoredProcCommandWithSourceColumns(updatestoredProcedureName, columnParams);

            int rowsAffected = db.UpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand,
                UpdateBehavior.Standard);

            updateCommand.Connection.Close();
            insertCommand.Connection.Close();
            deleteCommand.Connection.Close();

            return rowsAffected;
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName">The name of the insert stored procedure to execute.</param>
        /// <param name="dsProcParams">A dataset of parameters to pass to the stored procedure.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public static DataSet ExecuteStoredProc(string storedProcName, DataSet dsProcParams)
        {
            //SqlCommand sqlCommd = new SqlCommand();
            Database db;
            DbCommand dbCommd;
            db = DatabaseFactory.CreateDatabase();
            dbCommd = db.GetStoredProcCommand(storedProcName);

            try
            {
                //sqlCommd.CommandType = CommandType.StoredProcedure;
                //sqlCommd.CommandText = storedProcName;

                if (dsProcParams.Tables.Count > 0)
                {
                    PopulateDbCommandFromDataTable(ref dbCommd, dsProcParams.Tables[0]);
                }

                //return DB.ExecuteDataSet(sqlCommd);

                return DB.ExecuteDataSet(dbCommd);
            }
            finally
            {
                if (dbCommd.Connection.State != ConnectionState.Closed) { dbCommd.Connection.Close(); }
                dbCommd.Dispose();
                if (db != null) db = null;
                //sqlCommd.Dispose();
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes the named Stored Procedure with no parameters and returns multiple named DataTables
        /// </summary>
        /// <remarks>Uses the MS Patterns and Practices Data Access Application Block via the NET.Library Helper.</remarks>
        /// <param name="storedProcedureName">String name of the SQL Stored Proc</param>
        /// <param name="tableNames">Comma delimeted list of names to assign to newly created tables</param>
        /// <returns>Dataset from the website database</returns>
        ///----------------------------------------------------------------------------------
        public static DataSet ExecuteStoredProc(string storedProcedureName, string[] tableNames)
        {
            object[] emptyArray = new object[0];
            return ExecuteStoredProc(storedProcedureName, emptyArray, tableNames);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes the named Stored Procedure with no parameters and returns multiple named DataTables
        /// </summary>
        /// <remarks>Uses the MS Patterns and Practices Data Access Application Block via the NET.Library Helper.</remarks>
        /// <param name="storedProcedureName">String name of the SQL Stored Proc</param>
        /// <param name="parameterValues"></param>
        /// <param name="tableNames">Comma delimeted list of names to assign to newly created tables</param>
        /// <returns>Dataset from the website database</returns>
        ///----------------------------------------------------------------------------------
        public static DataSet ExecuteStoredProc(string storedProcedureName, object[] parameterValues, string[] tableNames)
        {
            DataSet returnDS = DB.ExecuteDataSet(storedProcedureName, parameterValues);
            for (int i = 0; i < returnDS.Tables.Count; i++)
            {
                if (tableNames[i] != null)
                {
                    returnDS.Tables[i].TableName = tableNames[i].Trim();
                }
            }
            return returnDS;
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// PopulateDbCommandFromDataTable
        /// </summary>
        /// <param name="dbCommand"></param>
        /// <param name="pDataTable"></param>
        ///----------------------------------------------------------------------------------
        public static void PopulateDbCommandFromDataTable(ref DbCommand dbCommand, DataTable pDataTable)
        {
            if (pDataTable.Rows.Count > 0)
            {
                PopulateDbCommandFromDataRow(ref dbCommand, pDataTable.Columns, pDataTable.Rows[0]);
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbCommand"></param>
        /// <param name="pColCollection"></param>
        /// <param name="pDatarow"></param>
        ///----------------------------------------------------------------------------------
        public static void PopulateDbCommandFromDataRow(ref DbCommand dbCommand, DataColumnCollection pColCollection, DataRow pDatarow)
        {
            for (int i = 0; i < pColCollection.Count; i++)
            {
                DbParameter prm = dbCommand.CreateParameter();
                try
                {
                    prm.ParameterName = "@" + pColCollection[i].ColumnName;
                    prm.Value = pDatarow.ItemArray[i];

                    dbCommand.Parameters.Add(prm);
                    //.AddWithValue("@" + pColCollection[i].ColumnName, pDatarow.ItemArray[i]);
                }
                finally
                {
                    prm = null;
                }
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Wrapper method to return a StoredProcCommand.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns>DbCommand</returns>
        ///----------------------------------------------------------------------------------
        public static DbCommand GetStoredProcCommand(string storedProcedureName)
        {
            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                return db.GetStoredProcCommand(storedProcedureName);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Wrapper method to return a StoredProcCommand.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues"></param>
        /// <returns>DbCommand</returns>
        ///----------------------------------------------------------------------------------
        public static DbCommand GetStoredProcCommand(string storedProcedureName, params object[] parameterValues)
        {
            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                return db.GetStoredProcCommand(storedProcedureName, parameterValues);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        public static void AddInParameter(DbCommand dbCommand, string name, DbType dbType)
        {
            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                db.AddInParameter(dbCommand, name, dbType);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        public static void AddInParameter(DbCommand dbCommand, string name, DbType dbType, object value)
        {
            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                db.AddInParameter(dbCommand, name, dbType, value);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        public static void AddOutParameter(DbCommand dbCommand, string name, DbType dbType, int size)
        {
            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                db.AddOutParameter(dbCommand, name, dbType, size);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        /// <summary>
        /// AddOutParameter default method which parses the parameter as an integer.
        /// </summary>
        /// <param name="dbCommand"></param>
        /// <param name="name"></param>
        public static void AddOutParameter(DbCommand dbCommand, string name)
        {
           DB.AddOutParameter(dbCommand, name, DbType.Int32, 4);
        }

        public static void AddReturnValueParameter(DbCommand dbCommand, string name, DbType dbType, int size, string sourceColumn)
        {
            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                db.AddParameter(dbCommand, name, dbType, size, ParameterDirection.ReturnValue, false, 0, 0, sourceColumn, DataRowVersion.Current, null);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        public static bool ColumnExists(IDataReader reader, string columnName)
        {
            //if (reader.GetSchemaTable().Columns.Contains("columnName"))
            //{
            //    return true;
            //}

            //return false;

            reader.GetSchemaTable().DefaultView.RowFilter = string.Format("ColumnName= '{0}'", columnName);
            return (reader.GetSchemaTable().DefaultView.Count > 0);
        }
        #endregion

        #endregion
    }
}
